﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using EnvironmentManagerSystem.MODE;

namespace EnvironmentManagerSystem.BLL
{
    public class BasicData
    {
        /// <summary>
        /// 取出全部大分类
        /// </summary>
        /// <returns>大类集合</returns>
        public static BasicDataMainDataCollection GetBigList()
        {
            try
            {
                BasicDataMainDataCollection lst = new BasicDataMainDataCollection();
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" tbs001_basicdatamain.id, ");
                br.Append(" tbs001_basicdatamain.DataKindName, ");
                br.Append(" tbs001_basicdatamain.DataKindMEMO  ");
                br.Append(" from tbs001_basicdatamain");
                DataTable dt= DbHelperMySQL.Query(br.ToString()).Tables[0];
                for (int i = 0; i < dt.Rows.Count;i++)
                {
                    BasicDataMainData model = new BasicDataMainData();
                    model.ID = Convert.ToInt32(dt.Rows[i].ItemArray[0]);
                    model.DataKindName = dt.Rows[i].ItemArray[1].ToString();
                    model.DataKindMEMO = dt.Rows[i].ItemArray[2].ToString();
                    lst.Add(model);
                }
                return lst;
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 获取对应大分类的子项数据集
        /// </summary>
        /// <param name="mainID">主类id</param>
        /// <returns>datatable</returns>
        public static DataTable GetSubKindByMainID(int mainID)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" tbs001_basicdatasub.id, ");
                br.Append(" tbs001_basicdatasub.MainID, ");
                br.Append(" tbs001_basicdatasub.DataValue,  ");
                br.Append(" tbs001_basicdatasub.DataValueMemo  ");
                br.Append(" from tbs001_basicdatasub ");
                br.AppendFormat(" where tbs001_basicdatasub.MainID={0} ",mainID.ToString());
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据主类名获取子类集合
        /// </summary>
        /// <param name="Name">主类名称</param>
        /// <returns>datatable</returns>
        public static DataTable GetSubKindByMainName(string Name)
        {
            try
            {
                string sql = "select * from tbs001_basicdatasub where MainID=(SELECT tbs001_basicdatamain.id FROM tbs001_basicdatamain WHERE tbs001_basicdatamain.DataKindName =  '"+Name+"')";
                return DbHelperMySQL.Query(sql).Tables[0];
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                return null;
            }
        }

        /// <summary>
        /// 根据名称获取主类id
        /// </summary>
        /// <param name="name">主类名</param>
        /// <returns>主类id</returns>
        public static int GetMainIDbyName(string name)
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append(" select ");
                br.Append(" tbs001_basicdatamain.id, ");
                br.Append(" tbs001_basicdatamain.DataKindName, ");
                br.Append(" tbs001_basicdatamain.DataKindMEMO  ");
                br.Append(" from tbs001_basicdatamain");
                br.AppendFormat(" where tbs001_basicdatamain.DataKindName='{0}' ", name);
                DataSet ds = DbHelperMySQL.Query(br.ToString());
                return Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[0]);
            }
            catch (Exception ex)
            {
                Console.Out.WriteLine(ex.Message);
                return 0;
            }
        }

        /// <summary>
        /// 插入主类数据
        /// </summary>
        /// <param name="model">主类对象</param>
        /// <returns>影响行数</returns>
        public static int InsertMainBasicData(BasicDataMainData model)
        {
            try
            {
                string sql = " insert into  tbs001_basicdatamain(DataKindName,DataKindMEMO) values('"+model.DataKindName+"','"+model.DataKindMEMO+"')";
                return DbHelperMySQL.ExecuteSql(sql);
                
            }
            catch(Exception ex)
            {
                return 0;
            }
        }

        /// <summary>
        /// 插入子类数据
        /// </summary>
        /// <param name="model">子类对象</param>
        /// <returns>影响行数</returns>
        public static int InsertSubBasicData(BasicDataSubData model)
        {
            try
            {
                string sql = " insert into  tbs001_basicdatasub(mainid,datavalue,datavaluememo) values('" + model.MainID + "','" + model.DataValue + "','"+model.DataValueMemo+"')";
                return DbHelperMySQL.ExecuteSql(sql);

            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        /// <summary>
        /// 根据名称删除主类
        /// </summary>
        /// <param name="name">要删除的主类名称</param>
        /// <returns>影响行数</returns>
        public static int DelMainKindByName(string name)
        {
            try
            {
                string sql = "delete from tbs001_basicdatamain where datakindname='"+name+"'";
                return DbHelperMySQL.ExecuteSql(sql);

            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        /// <summary>
        /// 根据名称更新主类名称
        /// </summary>
        /// <param name="oldName">旧名称</param>
        /// <param name="newName">新名称</param>
        /// <returns>影响行数</returns>
        public static int UpDataMainKindNameByName(string oldName,string newName)
        {
            try
            {
                string sql = "update tbs001_basicdatamain set DataKindName='"+newName+"' where DataKindName='"+oldName+"'";
                return DbHelperMySQL.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        /// <summary>
        /// 根据id删除子类
        /// </summary>
        /// <param name="id">子类id</param>
        /// <returns>影响行数</returns>
        public static int DeleteSubKind(string id)
        {
            int i = 0;
            try
            {
                i=DbHelperMySQL.ExecuteSql(string.Format(" delete from tbs001_basicdatasub where id='{0}'", id));
            }
            catch(Exception ex) 
            {

            }
            return i;
        }

        /// <summary>
        /// 根据id更新子类信息
        /// </summary>
        /// <param name="model">包含更改后数据的子类对象，id不能动</param>
        /// <returns>影响行数</returns>
        public static int UpDataSubKindByID(BasicDataSubData model)
        {
            try
            {
                string sql = "update tbs001_basicdatasub set DataValue='" + model.DataValue + "',DataValueMemo='"+model.DataValueMemo+"' where ID=" + model.ID + "";
                return DbHelperMySQL.ExecuteSql(sql);
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        #region DropDownList
        //查询难度系数结果
        public static DataTable GetNanDuXiaShuAll()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("select DataValue from tbs001_basicdatasub where MainID=28");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        //查询行业系数结果
        public static DataTable GetHangYeXiaShuAll()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("select DataValue from tbs001_basicdatasub where MainID=27");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        ////查询汇总上报书面材料
        public static DataTable GetXiangMuShangBaoAll()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("select ID,DataValue from tbs001_basicdatasub where MainID=29");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        //查询原则性不合格条款结果
        public static DataTable GetYuanZeXingBuHeGeTiaoKuanAll()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("select ID,DataValue from tbs001_basicdatasub where MainID=12");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }

        //查询项目状态
        public static DataTable GetProjectState()
        {
            try
            {
                StringBuilder br = new StringBuilder();
                br.Append("select ID,DataValue from tbs001_basicdatasub where MainID=32");
                return DbHelperMySQL.Query(br.ToString()).Tables[0];
            }
            catch { return null; }
        }
        #endregion
    }
}
